﻿Imports System.Data
Imports MySql.Data.MySqlClient

Public Class addpersonal
    Dim mysqlCon As MySqlConnection
    Dim mysqlCon1 As MySqlConnection
    Dim mysqlCon2 As MySqlConnection
    Dim mysqlCon3 As MySqlConnection
    Dim mysqlCon4 As MySqlConnection
    Dim mysqlCon5 As MySqlConnection
    Dim addNewCompany As String
    Public dept_evo As String

    Private Structure ExcelRows

        Dim C1 As String
        Dim C2 As String
        Dim C3 As String
        Dim C4 As String
        Dim C5 As String

    End Structure



    Private ExcelRowlist As List(Of ExcelRows) = New List(Of ExcelRows)

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim nextForm As main = New main()
        nextForm.Show()
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk

    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        With OpenFileDialog1
            .Filter = "Excel File  (*.xlsx) | *.xlsx"
            If (.ShowDialog() = DialogResult.OK) Then
                TextBox1.Text = .FileName
            End If


        End With


    End Sub

    Private Function getInfo() As Boolean
        ExcelRowlist.Clear()
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        Dim Excel As New Excel.Application
        If TextBox1.Text <> Nothing Then

            Excel.Workbooks.Open(TextBox1.Text)


            Excel.Sheets("Sheet4").Activate()
            Excel.Range("A4").Activate()

            Dim row As New ExcelRows

            Do
                If Excel.ActiveCell.Value > Nothing Or Excel.ActiveCell.Text > Nothing Then


                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C1 = Excel.ActiveCell.Value

                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C2 = Excel.ActiveCell.Value

                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C2 += " " + Excel.ActiveCell.Value

                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C2 += Excel.ActiveCell.Value

                    Excel.ActiveCell.Offset(0, 1).Activate()
                    row.C3 = Excel.ActiveCell.Value

                    Excel.ActiveCell.Offset(0, 5).Activate()
                    row.C4 = Excel.ActiveCell.Value

                    Excel.ActiveCell.Offset(0, -1).Activate()
                    row.C5 = Excel.ActiveCell.Value



                    ExcelRowlist.Add(row)

                    Excel.ActiveCell.Offset(1, -9).Activate()


                Else

                    Exit Do

                End If

            Loop

            Return True
        Else


        End If
        Excel.Quit()

    End Function

    ''Private Sub addpersonal_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    ''Dim mainForm As main = New main()
    ''  mainForm.Show()
    'Me.Close()
    ''End Sub

    Private Sub addpersonal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim sel_company_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_company_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_company_mysqlAdapter As New MySqlCommand
        Dim selectCompany As String

        selectCompany = "Select Distinct comp_name from department"

        mysqlCon = New MySqlConnection
        mysqlCon1 = New MySqlConnection
        mysqlCon2 = New MySqlConnection
        mysqlCon3 = New MySqlConnection
        mysqlCon4 = New MySqlConnection
        mysqlCon5 = New MySqlConnection

        mysqlCon.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        mysqlCon1.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        mysqlCon2.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        mysqlCon3.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        mysqlCon4.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        mysqlCon5.ConnectionString = "server=192.168.1.13;port = 3308;user id=" + "root" + ";password=" + "pisona" + ";database=testremote;Character Set =utf8"
        Try
            mysqlCon.Open()
            mysqlCon1.Open()
            mysqlCon2.Open()
            mysqlCon3.Open()
            mysqlCon4.Open()
            mysqlCon5.Open()


        Catch ex As Exception
            MsgBox("Can't Connect to database")
        End Try

        sel_company_mysqlAdapter.CommandText = selectCompany
        sel_company_mysqlAdapter.Connection = mysqlCon5
        sel_company_mysqlda.SelectCommand = sel_company_mysqlAdapter
        sel_company_mysqldr = sel_company_mysqlAdapter.ExecuteReader


        While sel_company_mysqldr.Read()
            ComboBox1.Items.Add(sel_company_mysqldr("comp_name"))
        End While

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If ComboBox1.Text.ToString <> "" And TextBox1.Text <> "" Then

            If selectgetInfo() Then
                For Each Xitem In ExcelRowlist
                    Dim ivite As ListViewItem

                Next
            End If
        Else
            MsgBox(" Please Choose Company  Or Browse File Excel")

        End If


    End Sub

    Private Function selectgetInfo() As Boolean
        'Sql Syntax MySQL
        Dim findDept As String
        Dim findMaxDept As String
        Dim insert_employee As String
        Dim insert_Department As String
        Dim findEmployee As String
        Dim dept_char As String
        Dim stringsplit() As String

        Dim Excel As New Excel.Application
        Dim deptid_int As Integer
        Dim deptid_string As String = ""

        Dim mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim mysqlAdapter As New MySqlCommand

        Dim max_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim max_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim max_mysqlAdapter As New MySqlCommand


        Dim sel_em_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim sel_em_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim sel_em_mysqlAdapter As New MySqlCommand
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

        Dim insert_em_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim insert_em_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim insert_em_mysqlAdapter As New MySqlCommand

        Dim insert_dept_mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim insert_dept_mysqldr As MySql.Data.MySqlClient.MySqlDataReader
        Dim insert_dept_mysqlAdapter As New MySqlCommand

        Dim deptString As String
        Dim devString As String
        Dim dept_id_string As String

        Dim emp_code_employee As String
        Dim name_employee As String
        Dim surname_employee As String
        Dim position_employee As String
        Dim startDate_employee As String
        Dim lv_employee As String
        Dim emp_gender As String
        Dim emp_thai_id As String


        Excel.Workbooks.Open(Me.TextBox1.Text)

        Excel.Sheets("Sheet4").Activate()
        Excel.Range("A4").Activate()

        Dim row As New ExcelRows

        Do
            If mysqlCon.State = ConnectionState.Closed Then
                mysqlCon.Open()
            End If

            If Excel.ActiveCell.Value > Nothing Or Excel.ActiveCell.Text > Nothing Then

                Excel.ActiveCell.Offset(0, 1).Activate()
                emp_code_employee = Excel.ActiveCell.Value

                Excel.ActiveCell.Offset(0, 1).Activate()
                emp_gender = Excel.ActiveCell.Value

                Excel.ActiveCell.Offset(0, 1).Activate()
                name_employee = Excel.ActiveCell.Value

                Excel.ActiveCell.Offset(0, 1).Activate()
                surname_employee = Excel.ActiveCell.Value


                Excel.ActiveCell.Offset(0, 1).Activate()
                startDate_employee = Excel.ActiveCell.Value

                Excel.ActiveCell.Offset(0, 1).Activate()
                emp_thai_id = Excel.ActiveCell.Value

                stringsplit = startDate_employee.Split("/")
                stringsplit(2) = stringsplit(2).Trim() - 543
                stringsplit(1) = stringsplit(1).Trim()
                stringsplit(0) = stringsplit(0).Trim()
                startDate_employee = stringsplit(2) + "-" + stringsplit(1) + "-" + stringsplit(0)


                Excel.ActiveCell.Offset(0, 1).Activate()
                devString = Excel.ActiveCell.Value
                'MsgBox(Excel.ActiveCell.Value)

                Excel.ActiveCell.Offset(0, 1).Activate()
                deptString = Excel.ActiveCell.Value

                Excel.ActiveCell.Offset(0, 1).Activate()
                position_employee = Excel.ActiveCell.Value

                Excel.ActiveCell.Offset(0, 1).Activate()
                lv_employee = Excel.ActiveCell.Value

                'MsgBox(Excel.ActiveCell.Value)
                findDept = "Select dept_id from testremote.department where dept_name = '" + deptString + "' and dev_name = '" + devString + "';"
                findMaxDept = "Select dept_id from testremote.department order by dept_id DESC limit 1;"



                mysqlAdapter.CommandText = findDept
                mysqlAdapter.Connection = mysqlCon
                mysqlda.SelectCommand = mysqlAdapter
                mysqldr = mysqlAdapter.ExecuteReader



                If mysqldr.Read() Then




                    dept_id_string = mysqldr("dept_id")

                    ''findMaxDept = "Select dept_id from testremote.department order by dept_id DESC limit 1;"
                    findEmployee = "Select emp_code from Employee where emp_code = '" & emp_code_employee & "' And emp_surname = '" & surname_employee & "' And dept_id = '" & dept_id_string & "' And emp_name = '" & name_employee & "';"

                    sel_em_mysqlAdapter.CommandText = findEmployee
                    sel_em_mysqlAdapter.Connection = mysqlCon4
                    sel_em_mysqlda.SelectCommand = sel_em_mysqlAdapter
                    sel_em_mysqldr = sel_em_mysqlAdapter.ExecuteReader

                    If sel_em_mysqldr.Read() Then

                    Else
                        insert_employee = "INSERT INTO employee (emp_code,dept_id,emp_name,emp_surname,emp_position,emp_level,emp_start_date,emp_gender,emp_thai_id,emp_status) VALUES ('" & emp_code_employee & "','" & dept_id_string & "' , '" & name_employee & "','" & surname_employee & "','" & position_employee & "','" & lv_employee & "', '" & startDate_employee & "','" & emp_gender & "', '" & emp_thai_id & "','1')"

                        insert_em_mysqlAdapter.CommandText = insert_employee
                        insert_em_mysqlAdapter.CommandType = CommandType.Text
                        insert_em_mysqlAdapter.Connection = mysqlCon3
                        insert_em_mysqlAdapter.ExecuteNonQuery()


                    End If
                    sel_em_mysqldr.Close()

                    'MsgBox(deptid_string)
                Else

                    max_mysqlAdapter.CommandText = findMaxDept
                    max_mysqlAdapter.Connection = mysqlCon1
                    max_mysqlda.SelectCommand = max_mysqlAdapter
                    max_mysqldr = max_mysqlAdapter.ExecuteReader


                    If max_mysqldr.Read() Then
                        'MsgBox(max_mysqldr("dept_id"))
                        deptid_int = max_mysqldr("dept_id")
                        'MsgBox(deptid_int)
                        deptid_int += 1


                        dept_char = CStr(deptid_int)

                        addZero(dept_char)

                        max_mysqldr.Close()
                        insert_employee = "INSERT INTO employee (emp_code,dept_id,emp_name,emp_surname,emp_position,emp_level,emp_start_date,emp_gender,emp_status) VALUES ('" & emp_code_employee & "','" & dept_evo & "' , '" & name_employee & "','" & surname_employee & "','" & position_employee & "','" & lv_employee & "', '" & startDate_employee & "','" & emp_gender & "','1')"
                        insert_Department = "INSERT INTO department (dept_id,dept_name,dev_name,comp_name) VALUES ('" & dept_evo & "','" & deptString & "', '" & devString & "', '" + ComboBox1.Text + "');"


                        insert_dept_mysqlAdapter.CommandText = insert_Department
                        insert_dept_mysqlAdapter.CommandType = CommandType.Text
                        insert_dept_mysqlAdapter.Connection = mysqlCon2
                        insert_dept_mysqlAdapter.ExecuteNonQuery()

                        findEmployee = "Select emp_code from Employee where emp_code = '" & emp_code_employee & "' And emp_surname = '" & surname_employee & "' And dept_id = '" & dept_evo & "';"

                        sel_em_mysqlAdapter.CommandText = findEmployee
                        sel_em_mysqlAdapter.Connection = mysqlCon4
                        sel_em_mysqlda.SelectCommand = sel_em_mysqlAdapter
                        sel_em_mysqldr = sel_em_mysqlAdapter.ExecuteReader

                        If sel_em_mysqldr.Read Then

                        Else

                            insert_em_mysqlAdapter.CommandText = insert_employee
                            insert_em_mysqlAdapter.CommandType = CommandType.Text
                            insert_em_mysqlAdapter.Connection = mysqlCon3
                            insert_em_mysqlAdapter.ExecuteNonQuery()

                        End If
                        sel_em_mysqldr.Close()
                        max_mysqldr.Close()


                    Else
                        insert_Department = "INSERT INTO department (dept_id,dept_name,dev_name,comp_name) VALUES ('00000','" & deptString & "', '" & devString & "', '" + ComboBox1.Text + "');"


                        insert_dept_mysqlAdapter.CommandText = insert_Department
                        insert_dept_mysqlAdapter.CommandType = CommandType.Text
                        insert_dept_mysqlAdapter.Connection = mysqlCon2
                        insert_dept_mysqlAdapter.ExecuteNonQuery()

                        insert_employee = "INSERT INTO employee (emp_code,dept_id,emp_name,emp_surname,emp_position,emp_level,emp_start_date,emp_gender,emp_status) VALUES ('" & emp_code_employee & "','00000' , '" & name_employee & "','" & surname_employee & "','" & position_employee & "','" & lv_employee & "', '" & startDate_employee & "','" & emp_gender & "','1')"
                        findEmployee = "Select emp_code from Employee where emp_code = '" & emp_code_employee & "' And emp_surname = '" & surname_employee & "' And dept_id = '" & dept_evo & "';"

                        sel_em_mysqlAdapter.CommandText = findEmployee
                        sel_em_mysqlAdapter.Connection = mysqlCon4
                        sel_em_mysqlda.SelectCommand = sel_em_mysqlAdapter
                        sel_em_mysqldr = sel_em_mysqlAdapter.ExecuteReader

                        If sel_em_mysqldr.Read Then

                        Else

                            insert_em_mysqlAdapter.CommandText = insert_employee
                            insert_em_mysqlAdapter.CommandType = CommandType.Text
                            insert_em_mysqlAdapter.Connection = mysqlCon3
                            insert_em_mysqlAdapter.ExecuteNonQuery()

                        End If
                        sel_em_mysqldr.Close()
                        max_mysqldr.Close()
                    End If
                    'MsgBox("Cannot")

                End If


                Excel.ActiveCell.Offset(1, -10).Activate()

            Else

                Exit Do

            End If

            mysqldr.Close()

        Loop
        MsgBox("Update Complete")
        Excel.Workbooks.Close()


    End Function

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ListView1.Items.Clear()


        If getInfo() Then
            For Each Xitem In ExcelRowlist
                Dim ivite As ListViewItem
                ivite = Me.ListView1.Items.Add(Xitem.C1)
                ivite.SubItems.AddRange(New String() {Xitem.C2, Xitem.C3, Xitem.C4, Xitem.C5})
            Next
        End If

    End Sub

    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

    End Sub


    Public Sub addZero(ByVal sud As String)

        Dim length As Integer

        length = Len(sud)

        If (length <= 5) Then


            If (length = 4) Then
                sud = "0" + sud
                dept_evo = sud
            End If
            If (length = 3) Then
                sud = "00" + sud
                dept_evo = sud
            End If
            If (length = 2) Then
                sud = "000" + sud
                dept_evo = sud
            End If
            If (length = 1) Then
                sud = "0000" + sud
                dept_evo = sud
            End If
        Else
            dept_evo = sud
        End If


    End Sub


    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        ''  If()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        addNewCompany = InputBox("Please Enter Your Company Name", "Add New Company", "")
        If addNewCompany.Length > 0 Then
            ComboBox1.Items.Add(addNewCompany)
        Else
            Exit Sub

        End If

    End Sub

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged

    End Sub
End Class